package br.com.ffm.praticas.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import br.com.ffm.praticas.bean.Address;
import br.com.ffm.praticas.connection.ConnectionDB;
import br.com.ffm.praticas.constants.Constant;

public class AddressDAO {
	
	/**
	 * Method that returns all Address
	 * @return List<Address>
	 * @throws SQLException
	 */
	public List<Address> getAddresses() throws SQLException{
		String query = "SELECT * FROM address";
		List<Address> list = new LinkedList<Address>();
		CityDAO cityDAO = new CityDAO();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			Address address = new Address();
			address.setId(result.getInt("id"));
			if (result.getString("street") != null)
				address.setStreet(result.getString("street"));
			if (result.getString("neighborhood") != null)
				address.setNeighborhood(result.getString("neighborhood"));
			address.setNumber(result.getInt("number"));
			if (result.getString("cep") != null)
				address.setCep(result.getString("cep"));
			address.setCity(cityDAO.getCity(result.getInt("city_id")));
			if (result.getString("complement") != null)
				address.setComplement(result.getString("complement"));
			list.add(address);
		}
		ConnectionDB.getInstance().close();
		return list;
	}
	
	/**
	 * Method that return a specific Address for your id
	 * @param id Address Identifier
	 * @return Address
	 * @throws SQLException
	 */
	public Address getAddress(int id) throws SQLException{
		String query = "SELECT * FROM address WHERE (id = " + id + " )";
		Address address;
		CityDAO cityDAO = new CityDAO();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			address = new Address();
			address.setId(result.getInt("id"));
			if (result.getString("street") != null)
				address.setStreet(result.getString("street"));
			if (result.getString("neighborhood") != null)
				address.setNeighborhood(result.getString("neighborhood"));
			address.setNumber(result.getInt("number"));
			if (result.getString("cep") != null)
				address.setCep(result.getString("cep"));
			address.setCity(cityDAO.getCity(result.getInt("city_id")));
			if (result.getString("complement") != null)
				address.setComplement(result.getString("complement"));
			ConnectionDB.getInstance().close();
			return address;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that inserts a Address
	 * @param address Address that will be inserted
	 * @return Address id
	 * @throws SQLException
	 */
	public int insert(Address address) throws SQLException {
		String query = "INSERT INTO address (street, neighborhood, number, cep, city_id, complement) values (";
		query += "'" + address.getStreet() + "', ";
		query += "'" + address.getNeighborhood() + "', ";
		query += address.getNumber() + ", ";
		query += "'" + address.getCep() + "', ";
		query += address.getCity().getId() + ", ";
		query += "'" + address.getComplement() + "')";
		ConnectionDB.getInstance().executeCommand(query);
		
		query = "SELECT MAX(id) FROM address";
		int addressId = ConnectionDB.getInstance().executeScalar(query, Constant.SCALAR_COMMAND_MAX, "id");
		return addressId;
	}
	
	/**
	 * Method that deletes a Address
	 * @param address Address that will be deleted
	 * @return true it will have been deleted or false if it will not be deleted
	 */
	public boolean delete(Address address) {
		String query = "DELETE FROM address WHERE (id = " + address.getId() + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Method that updates a Address
	 * @param address Address that will be updated
	 * @return true it will have been updated or false if it will not be updated
	 */
	public boolean update(Address address) {
		String query = "UPDATE address SET ";
		query += "street = '" + address.getStreet() + "', ";
		query += "neighborhood = '" + address.getNeighborhood() + "', ";
		query += "number = " + address.getNumber() + ", ";
		query += "cep = '" + address.getCep() + "', ";
		query += "city_id = " + address.getCity().getId() + ", ";
		query += "complement = '" + address.getComplement() + "' ";
		query += "WHERE (id = " + address.getId() + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}

}
